<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
	<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
	<title>get_sql_dependencies: common_schema documentation</title>
	<meta name="description" content="get_sql_dependencies: common_schema" />
	<meta name="keywords" content="get_sql_dependencies: common_schema" />
	<link rel="stylesheet" type="text/css" href="css/style.css" />
</head>

<body>
	<div id="main">
		<div id="header">
			<h1>common_schema</h1> <strong>2.2</strong> documentation
			<div class="subtitle">DBA's framework for MySQL</div>
		</div>
		<div id="contentwrapper">
			<div id="content">
				<h2><a href="get_sql_dependencies.html">get_sql_dependencies</a></h2>	
<h3>NAME</h3>
get_sql_dependencies(): Analyze and list the dependencies of a given query (BETA)

<h3>TYPE</h3>
Procedure

<h3>DESCRIPTION</h3>
<p>
This procedure will analyze the given query, and provide with dependency listing: 
the objects on which this query depends, e.g. tables, routines, views etc.
</p>
<p>
<i>get_sql_dependencies()</i> will parse the query's text to detect such objects.
It will not validate their existence or correctness. It will not perform deep search in
order to further find dependencies of those objects.
</p>
<p>
Thus, this routines does not actually perform any SQL operations, other than create and use internal
temporary structures. 
It will not access INFORMATION_SCHEMA nor any other metadata.
</p>
<p>
It is not, and will not be, able to parse dynamic SQL, i.e. prepared statements made from 
string literals. 
</p>
<p>
This procedure serves as the basis to other analysis routines.
</p>
<p>This code is in BETA stage.</p>

<h3>SYNOPSIS</h3>
<p>
<blockquote><pre>get_sql_dependencies(
    IN p_sql               TEXT charset utf8
,   IN p_default_schema    VARCHAR(64) charset utf8
)
DETERMINISTIC
</pre></blockquote>
</p>

<p>
  Input:
  <ul>
    <li><strong>p_sql</strong>: query to analyze</li>
    <li><strong>p_default_schema</strong>: schema context to assume for query</li>
  </ul>
</p>


<h3>STRUCTURE</h3>
<p>
The procedure returns a result set of dependencies for this routine:
  <ul>
    <li><strong>schema_name</strong>: schema where dependency is located.</li>
    <li><strong>object_name</strong>: name of dependency object.</li>
    <li><strong>object_type</strong>: type of dependency object (e.g. <strong>'table'</strong>, <strong>'function'</strong> etc.).</li>
    <li><strong>action</strong>: type of action performed on object (e.g. <strong>'select'</strong>, <strong>'call'</strong> etc.).</li>
  </ul>
</p>

<h3>EXAMPLES</h3>
	<p>
		Analyze a <strong>CREATE VIEW</strong> query:
	</p>
	<blockquote><pre>mysql&gt; call get_sql_dependencies('CREATE VIEW sakila.simple_actor AS SELECT actor_id, first_name FROM sakila.actor', 'sakila');

+-------------+--------------+-------------+--------+
| schema_name | object_name  | object_type | action |
+-------------+--------------+-------------+--------+
| sakila      | actor        | table       | select |
| sakila      | simple_actor | view        | create |
+-------------+--------------+-------------+--------+
</pre></blockquote>

<h3>ENVIRONMENT</h3>
MySQL 5.1 or newer

<h3>SEE ALSO</h3>
<a href="get_event_dependencies.html">get_event_dependencies()</a>,
<a href="get_routine_dependencies.html">get_routine_dependencies()</a>,
<a href="get_view_dependencies.html">get_view_dependencies()</a>

<h3>AUTHOR</h3>
Roland Bouman
				<br/>
			</div>
			<div id="sidebarwrapper">
				<div id="search">
					Search online documentation
					<form id="search_form" name="search_form" method="GET" 
						action="http://www.google.com/search" 
						onsubmit="document.forms['search_form']['q'].value = 'site:http://common-schema.googlecode.com/svn/trunk/common_schema/doc/html/ '+document.forms['search_form']['search_term'].value;">
						<input type="text" name="search_term" value=""/>
						<input type="hidden" name="q" value=""/>
						<input type="submit" value="go"/>						
					</form>
				</div>
				<div id="menu">
					<ul>
						<li><a title="Introduction" href="introduction.html">Introduction</a></li>
						<li><a title="Documentation" href="documentation.html">Documentation</a></li>
						<li><a title="Download" href="download.html">Download</a></li>
						<li><a title="Install" href="install.html">Install</a></li>
						<li><a title="Risks" href="risks.html">Risks</a></li>
					</ul>						
					<h3>QUERY SCRIPT</h3>
					<ul>
						<li><a title="QueryScript" href="query_script.html">QueryScript</a></li>
						<li><a title="Execution" href="query_script_execution.html">Execution</a></li>
						<li><a title="Flow control" href="query_script_flow_control.html">Flow control</a></li>
						<li><a title="Statements" href="query_script_statements.html">Statements</a></li>
						<li><a title="Expressions" href="query_script_expressions.html">Expressions</a></li>
						<li><a title="Variables" href="query_script_variables.html">Variables</a></li>
					</ul>						
					<h3>DEBUG</h3>
					<ul>
						<li><a title="rdebug" href="rdebug.html">rdebug</a></li>
						<li><a title="rdebug API" href="rdebug_api.html">rdebug API</a></li>
						<li><a title="rdebug workflow" href="rdebug_workflow.html">Workflow</a></li>
					</ul>						
					<h3>ROUTINES</h3>
					<ul>
						<li><a title="Execution &amp; flow control" href="execution_routines.html">Execution & flow control</a></li>
						<li><a title="General" href="general_routines.html">General</a></li>
						<li><a title="Process" href="process_routines.html">Process</a></li>
						<li><a title="Query analysis" href="query_analysis_routines.html">Query analysis</a></li>
						<li><a title="Schema analysis" href="schema_analysis_routines.html">Schema analysis</a></li>
						<li><a title="Security" href="security_routines.html">Security</a></li>
						<li><a title="Text" href="text_routines.html">Text</a></li>
						<li><a title="Time &amp; date" href="temporal_routines.html">Time & date</a></li>
						<li><a title="Charting" href="charting_routines.html">Charting</a></li>
					</ul>
					<h3>VIEWS</h3>
					<ul>
						<li><a title="Schema analysis" href="schema_analysis_views.html">Schema analysis</a></li>
						<li><a title="Data dimension" href="data_dimension_views.html">Data dimension</a></li>
						<li><a title="Process" href="process_views.html">Process</a></li>
						<li><a title="Security" href="security_views.html">Security</a></li>
						<li><a title="Monitoring" href="monitoring_views.html">Monitoring</a></li>
						<li><a title="InnoDB Plugin" href="innodb_plugin_views.html">InnoDB Plugin</a></li>
						<li><a title="Percona server" href="percona_server_views.html">Percona Server</a></li>
						<li><a title="TokuDB" href="tokudb_views.html">TokuDB</a></li>
					</ul>						
					<h3>DATA</h3>
					<ul>
						<li><a title="tables" href="tables.html">Tables</a></li>
						<li><a title="variables" href="variables.html">Variables</a></li>
					</ul>						
					<h3>META</h3>
					<ul>
						<li><a title="Help" href="help.html">help</a></li>
						<li><a title="Metadata" href="metadata.html">metadata</a></li>
						<li><a title="status" href="status.html">status</a></li>
					</ul>						
				</div>
			</div>	
			<div class="clear">&nbsp;</div>
			
			<div id="footnote" align="center">
				<a href="">common_schema</a> documentation
			</div>
		</div>
	</div>
</body>
</html>
